﻿/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE [CaseManagement].[CasesForVolunteer]( @VolunteerID BIGINT )
AS  
BEGIN

	Select Grants.OID as 'GrantsOID', 
			Grants.Version as 'GrantsVersion', 
			Grants.CaseID, 
			Grants.ApplicantID, 
			Grants.ValueOfGrant, 
			Grants.CheckNumber, 
			Grants.NameOnCreditCard, 
			Grants.GrantTypeID, 
			Grants.StateID, 
			Grants.PaymentTypeID, 
			Grants.DeliveryMethodID, 
			Grants.DeliverToPersonID, 
			Grants.DeliverToAddressID, 
			Grants.Approver1, 
			Grants.ApprovalDate1, 
			Grants.Approver2, 
			Grants.ApprovalDate2, 
			Grants.DisbursedBy, 
			Grants.DispersalDate, 
			Grants.VerifiedBy, 
			Grants.VerificationDate,

			Cases.OID as 'CasesOID', 
			Cases.Version as 'CasesVersion', 
			Cases.ServiceManID, 
			Cases.CaseVolunteerID, 
			Cases.CaseDirectorID,
			Cases.InjuryDate,

			ServiceMen.OID as 'ServiceMenOID', 
			ServiceMen.Version as 'ServiceMenVersion', 
			ServiceMen.PrimaryAddressID, 
			ServiceMen.PermanentAddressID,

			Applicants.OID as 'ApplicantsOID', 
			Applicants.Version as 'ApplicantsVersion', 
			Applicants.KinshipID, 
			Applicants.AddressID,

			People.OID as 'PeopleOID', 
			People.Version as 'PeopleVersion', 
			People.FirstName, 
			People.LastName, 
			People.MiddleInitial, 
			People.DOB, 
			People.Sex, 
			People.Phone1, 
			People.Phone2, 
			People.eMailAddress,

			Users.OID as 'UsersOID', 
			Users.GUID, 
			Users.FullName, 
			Users.UPN, 
			Users.LastSignedIn

	From Grants 
	Join Cases On Grants.CaseID = Cases.OID
	Join ServiceMen On ServiceMen.OID = Cases.ServiceManID
	Join Applicants On Grants.ApplicantID = Applicants.OID
	Join People On Applicants.OID = People.OID
	Join Users On Users.OID = Cases.CaseVolunteerID
	Where
		Cases.CaseVolunteerID = @VolunteerID

END


